const express = require("express")
const router = express.Router()
const time = require("../../utils/time")
const db = require("../../sqlServer/dbConnect")
const xlsx = require('node-xlsx')
const multer = require('multer')
const upload = multer({ dest: './public/upload/' });
const fs = require('fs')
const path = require('path')

// 食品基本信息-- 查询
router.post("/info", (req, res) => {
    const { values, pageConfig, doName } = req.body;
    const { page, pageSize } = pageConfig
    let sql = values === null ? "select * from good limit ?,?;select * from good" :
        "select * from good where name=? or company=? or createtime=? limit ?,?;select * from good where name=? or company=? or createtime=?"
    const data = values === null ? [(Number(page) - 1) * Number(pageSize), Number(pageSize)] :
        [values.fname, values.fcomp, values.ftime, (Number(page) - 1) * Number(pageSize), Number(pageSize), values.fname, values.fcomp, values.ftime]
    db.sqlQuery(sql, data, (err, result) => {
        if (err) {
            console.log(err)
            res.send({
                // data: result,
                msg: "获取信息失败",
                status: 203
            })
        } else {
            res.send({
                data: result[0],
                total: result[1].length,
                msg: "获取信息成功",
                status: 200
            })
        }
    })
    db.sqlQuery("insert into log(doName,doInfo,doTime) values(?,?,?)", [doName, "食品信息查询", time.getData()])
})

// 食品基本信息-- 添加
router.post("/info/add", (req, res) => {
    const { name, brand, company, legalperson, phone, doName } = req.body
    const code = parseInt(Math.random() * 100000000)
    const createtime = time.getDay()
    let sql = "insert into good(name,brand,code,company,legalperson,phone,createtime) values(?,?,?,?,?,?,?)"
    const data = [name, brand, code, company, legalperson, phone, createtime]
    db.sqlQuery(sql, data, (err) => {
        if (err) {
            console.log(err)
            if (err.errno === 1062) {
                console.log(err.sqlState)
                res.send({
                    msg: "添加失败，该食品已存在，请重新添加！",
                    status: 203
                })
            } else if (err.errno === 1048) {
                res.send({
                    msg: "添加失败,不能为空!",
                    status: 203
                })
            }
        } else {
            db.sqlQuery(`select * from good where code=?;select * from good where name=?`, [code, name], (err, codeList) => {
                if (err) {
                    console.log(err)
                } else {
                    if (codeList && codeList[0].length > 1) {
                        console.log(codeList)
                        res.send({
                            msg: "添加失败，食品编码冲突，请重新添加！",
                            status: 203
                        })
                    } else {
                        res.send({
                            msg: "添加成功",
                            status: 200
                        })
                    }
                }
            })
        }
    })
    db.sqlQuery("insert into log(doName,doInfo,doTime) values(?,?,?)", [doName, "食品信息添加", time.getData()])
})

// 食品基本信息-- 编辑
router.post("/info/edit", (req, res) => {
    const { name, brand, company, legalperson, phone, Id, doName } = req.body
    let sql = "update good set name=?,brand=?,company=?,legalperson=?,phone=? where Id=?"
    const data = [name, brand, company, legalperson, phone, Id]
    db.sqlQuery(sql, data, (err) => {
        if (err) {
            console.log(err)
            res.send({
                msg: "编辑失败",
                status: 203
            })
        } else {
            res.send({
                msg: "编辑成功",
                status: 200
            })
        }
    })
    db.sqlQuery("insert into log(doName,doInfo,doTime) values(?,?,?)", [doName, "食品信息编辑", time.getData()])
})

// 食品基本信息-- 删除
router.post("/info/delete", (req, res) => {
    const { Id, doName } = req.body
    let sql = "delete from good where Id=?"
    const data = [Id]
    db.sqlQuery(sql, data, (err) => {
        if (err) {
            console.log(err)
            res.send({
                msg: "删除失败",
                status: 203
            })
        } else {
            res.send({
                msg: "删除成功",
                status: 200
            })
        }
    })
    db.sqlQuery("insert into log(doName,doInfo,doTime) values(?,?,?)", [doName, "食品信息删除", time.getData()])
})

// 首页根据追溯码-- 查询
router.post("/info/code", (req, res) => {
    const { code } = req.body
    let sql = `SELECT g.Id,g.name,g.brand,g.legalperson,g.code,g.company,g.phone,g.createtime,
    s.purchase,s.verify,s.source,s.storage,s.provider,s.screatetime,
    p.environment,p.period,p.amount,p.date,p.qgp,
    i.insSmell,i.insImpurity,i.insComposition,i.insHeavyMetal,i.insBiomarker,i.insToxicSub,i.insPerson,i.insCompany,
    i.insCreatetime,i.insVerify,i.insRemark,
    m.traNum,m.traSpecs,m.traPerson,m.vehicle,m.traMode,m.traSendTime,m.traRecTime,m.deliveryPlace,m.recievePlace,
    m.distributor,m.distributorPer,m.distributorTel,
    r.reSpecs,r.reNum,r.reSource,r.reReason,r.reStatus,r.rePlan,r.reTime FROM good AS g 
    LEFT JOIN source AS s ON g.name=s.name 
    LEFT JOIN product AS p ON s.Id=p.sId 
    LEFT JOIN inspection AS i ON p.Id=i.pId 
    LEFT JOIN moveinfo AS m ON i.Id=m.insId 
    LEFT JOIN recall AS r ON m.Id=r.moId 
    WHERE g.code=?`
    if (code === undefined || code === "" || code === null) {
        res.send({
            msg: "请输入正确的追溯码！",
            status: 203
        })
        return false
    } else {
        db.sqlQuery(sql, [code], (err, food) => {
            if (err) {
                console.log(err)
                res.send({
                    // data: result,
                    msg: "获取信息失败",
                    status: 203
                })
                return false
            } else {
                if (food && food.length <= 0) {
                    res.send({
                        msg: "没有该食品信息,请输入正确的追溯码！",
                        status: 203
                    })
                } else {
                    data = food
                }
            }
            if (data.length <= 0) {
                return false
            } else {
                res.send({
                    data: food,
                    msg: "成功",
                    status: 200
                })
            }
        })
    }

})

// 食品基本信息导入
router.post('/info/upload', upload.single('file'), (req, res) => {
    const oldPath = req.file.destination + '/' + req.file.filename;
    const newPath = req.file.destination + '/' + req.file.filename + req.file.originalname;
    fs.rename(oldPath, newPath, () => {
        const obj = xlsx.parse(newPath)
        obj[0].data && obj[0].data.forEach((item, index) => {
            if (index >= 1) {
                const code = parseInt(Math.random() * 100000000)
                const createtime = time.getDay()
                let sql = "insert into good(name,brand,company,legalperson,phone,code,createtime) values(?,?,?,?,?,?,?)"
                const data = [].concat(item, [code, createtime])
                db.sqlQuery(sql, data, (err, result) => {
                    if (err) {
                        fs.unlink(newPath, function () {
                            if (index >= obj[0].data.length - 1) {
                                res.send({ msg: "上传失败，名称或编码相同！", status: 203 })
                            }
                        })
                        return
                    } else {
                        if (index >= obj[0].data.length - 1) {
                            fs.unlink(newPath, function () {
                                res.send({ msg: "上传成功", status: 200 })
                            })
                        }
                    }
                })
            }
        })

    });
})

router.get('/info/export', (req, res) => {
    const { page, pageSize } = req.query;
    let sql = "select * from good limit ?,?"
    const data = [(Number(page) - 1) * Number(pageSize), Number(pageSize)]
    db.sqlQuery(sql, data, (err, result) => {
        if (err) {
            res.send({
                msg: "获取信息失败",
                status: 203
            })
        } else {
            const title = ['名称', '品牌', '编码', '公司', '法定代表人', '联系电话', '创建时间']
            const sqlData = []
            sqlData.push(title)
            result.forEach(item => {
                const row = []
                row.push(item.name)
                row.push(item.brand)
                row.push(item.code)
                row.push(item.company)
                row.push(item.legalperson)
                row.push(item.phone)
                row.push(item.createtime)
                sqlData.push(row)
            })
            const buffer = xlsx.build([{ name: `食品基本信息${time.preExcel()}`, data: sqlData }]);
            const excPath = `${path.resolve(__dirname,`../../../public/download/食品基本信息${time.preExcel()}.xlsx`)}`
            fs.writeFileSync(excPath, buffer, 'binary');
            res.download(excPath);
        }
    })
})

module.exports = router